package cn.kgc.jdbc;

import com.mysql.jdbc.Driver;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

/**
 * @author: mosin
 * @version: v1.0  2022/4/18
 * jdbc 操作数据库的一套接口 (规范) 具体的实现有各大数据库厂商完成
 * 使用JDBC连接数据库  java - > mysql  -> sql语句
 * 步骤：
 *      1. 加载驱动 (实现类)
 *      2.获取数据库连接
 *      3.获取操作数据库的对象
 *      4.编写sql语句,并通过操作数据库的对象发送sql
 *      5.处理结果集 （select）     update  insert  delete  -> 影响的行数  line
 *      6.关闭连接  释放资源
 */
public class JDBCDemo01 {
    //快速生成主方法 psvm   main
    public static void main(String[] args) {
        test07();
    }

    public  static void test01(){ // 获取数据库连接的方式1

        try {
            //创建驱动对象
            Driver driver = new Driver();
            //注册驱动
            DriverManager.registerDriver(driver);

            /**
             * 获取连接   getConnection(String url,String user ,String password)
             * url:    http://www.baidu.com
             * 参数1：   数据库连接的地址
             *         统一资源定位符  jdbc:mysql://localhost:3306/数据库名
             *          jdbc:mysql:  通信的协议
             *          localhost:3306： 数据库的地址和端口号
             * 参数2： 数据库的用户名  root
             * 参数3：数据库的密码    root
             * */

            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            Connection connection = DriverManager.getConnection(url,user,password);
            System.out.println("connection = " + connection);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public  static void test02() { // 获取数据库连接的方式2   推荐

        try {
            //通过反射完成驱动的注册
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            Connection connection = DriverManager.getConnection(url,user,password);
            System.out.println("connection = " + connection);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public  static void test03() { // 获取操作数据库的对象 操作数据库  crud
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            //通过反射完成驱动的注册
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url,user,password);

            //获取操作数据库的对象
            statement = connection.createStatement();
            //数据库的查询操作
            String sql = "select *  from  usr";
            // 发送sql 接收结果集
            resultSet = statement.executeQuery(sql);
            //处理结果集  resultSet.next()  ->  boolean  true  ->处理结果
            ArrayList<User> users = new ArrayList<>();
            while (resultSet.next()){ // 获取当前记录的每一个字段
                //根据当前字段的数据类型以及字段名获取字段值
                int id = resultSet.getInt("id");
                //通过列的索引和数据类型获取字段值   1
                String uname = resultSet.getString(2);
                String pwd = resultSet.getString(3);

              /*  System.out.println("id = " +id);
                System.out.println("uname = " + uname);
                System.out.println("pwd = " + pwd);*/

                User usr = new User(id, uname, pwd);
                users.add(usr);
                System.out.println("usr = " + usr);
                System.out.println("---------------------------------------");
            }



        } catch (Exception e) {
            e.printStackTrace();
        }finally { // 释放资源
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement!=null){

                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }

            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }


    public  static void test04() { //使用jdbc插入数据到数据库
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);
            //获取操作数据库的对象
            statement = connection.createStatement();
            //插入数据的sql
            String sql = "insert into usr(id,uname,pwd)values(10,'mayun','888888')";
            //返回值是影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"插入成功":"插入失败");

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }


    }


    public  static void test05() { //使用jdbc删除数据数据库中的一条记录
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);
            //获取操作数据库的对象
            statement = connection.createStatement();
            //插入数据的sql
            String sql = "delete from usr where  id = 10";
            //返回值是影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"删除成功":"删除失败");

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }


    public  static void test06() { //使用jdbc更新数据数据库中的一条记录
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);
            //获取操作数据库的对象
            statement = connection.createStatement();
            //插入数据的sql
            String sql = "update usr set pwd = '888888' where id = 12";
            //返回值是影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i>0?  "更新成功":"更新失败");

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }


    public  static void test07() {//SQL注入

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java2215?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            //获取连接
            Connection connection = DriverManager.getConnection(url, user, password);
            Statement statement = connection.createStatement();

            // 创建扫描器 实现用户的输入  用户名 密码
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名:");
            String  username = scanner.nextLine();
            System.out.println("请输入密码:");
            String  passwd = scanner.nextLine();

            //编写sql
            String sql = "select *  from usr where uname = '"+username+"' and pwd = '"+passwd+"'";
            //打印sql语句    sql注入  username = mayun  pwd = 2222'or'1=1
            System.out.println("sql = " + sql);  //select *  from usr where uname = 'mayun' and pwd = '2222'or'1=1'
            ResultSet resultSet = statement.executeQuery(sql);
            User usr = null;
            while (resultSet.next()){

                int id = resultSet.getInt("id");
                String uname = resultSet.getString("uname");
                String pwd = resultSet.getString("pwd");
                usr = new User(id, uname, pwd);

            }

            //判定用户是否存在
            if(usr!=null){
                System.out.println("登录成功");
            }else{
                System.out.println("登录失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }


    }


}
